package com.dcdata.xian;

import com.alibaba.fastjson.JSON;
import com.dcdata.xian.pojo.GjznCard;
import com.dcdata.xian.pojo.GjznRoute;
import com.dcdata.xian.pojo.GjznStation;
import com.dcdata.xian.utils.BeanUtil;
import lombok.SneakyThrows;
import oracle.jdbc.replay.OracleDataSource;
import oracle.jdbc.replay.OracleDataSourceImpl;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Calendar;
import java.util.Date;
import java.util.Timer;
import java.util.TimerTask;

/**
 * @author: xinmi
 * @date: 2020/11/19
 * @description:
 */
public class MysqlInsert {

    private static String user = "GJXWGH";
    private static String password = "GJXWGH20201116";
    private static String url = "jdbc:oracle:thin:@//10.238.161.162:1521/ORCLPDB";

    private static String cardFileName;

    private MysqlOperation mysqlOperation;

    private static String queryDate;
    private static String queryEndDate;

    private static MysqlInsert mysqlInsert;

    private static boolean firstTime = true;

    public static void main(String[] args) throws SQLException, ParseException {
        //dedao santian qian ca de shuju.
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        c.add(Calendar.DATE, -3);
        Date time = c.getTime();
        queryDate = sdf.format(time);

        Calendar cEnd = Calendar.getInstance();
        cEnd.add(Calendar.DATE, -2);
        Date timeEnd = cEnd.getTime();
        queryEndDate = sdf.format(timeEnd);


        if (cardFileName == null || cardFileName.isEmpty()) {
            cardFileName = System.getProperty("user.dir") + "/data/card/Card" + queryDate.replace("-", "_") + ".txt";
        }

        generateFileNameSchedule();

        mysqlInsert = new MysqlInsert();
        mysqlInsert.init();
        mysqlInsert.run();
        mysqlInsert.closeDb();

        firstTime = false;
    }

    private static void generateFileNameSchedule() {
        File dataDir = new File(System.getProperty("user.dir") + "/data");
        if (!dataDir.exists() && !dataDir.isDirectory())
            if (!dataDir.mkdir()) {
                System.out.println("mkdir data failed");
            }

        File cardDir = new File(System.getProperty("user.dir") + "/data/card");
        if (!cardDir.exists() && !cardDir.isDirectory())
            if (!cardDir.mkdir()) {
                System.out.println("mkdir data/card failed");
            }

        Timer timer = new Timer();
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.HOUR_OF_DAY, Config.GPS_HOUR); //4点
        calendar.set(Calendar.MINUTE, Config.GPS_MINUTE);//0分
        calendar.set(Calendar.SECOND, Config.GPS_SECOND);//0秒
        //每天的凌晨4:45,重新生成新文件
        timer.scheduleAtFixedRate(new TimerTask() {
            @SneakyThrows
            @Override
            public void run() {
                //每天定时生成新的文件路径和相应的输出流
                if (firstTime == false) {
                    cardFileName = System.getProperty("user.dir") + "/data/card/Card" + queryDate.replace("-", "_") + ".txt";
                    show("run in timer.");
                    mysqlInsert.init();
                    mysqlInsert.run();
                    mysqlInsert.closeDb();
                }
            }
        }, calendar.getTime(), 24 * 60 * 60 * 1000);
    }

    static Connection getConnection() throws SQLException {
        OracleDataSource ods = new OracleDataSourceImpl();
        ods.setURL(url);
        ods.setUser(user);
        ods.setPassword(password);
        Connection conn = ods.getConnection();
        return conn;
    }

    void init() {
        if (mysqlOperation == null)
            mysqlOperation = new MysqlOperation();
        mysqlOperation.connectToDatabase();
    }

    void closeDb() {
        mysqlOperation.closeDatabase();
    }

    void run() throws ParseException {
        try (Connection conn = getConnection()) {

            if (firstTime) {
                show("before receive 2020-12-01 card table.");
                cardTableOneDate(conn, "2020-12-01", "2020-12-02");

                show("before receive 2020-12-02 card table.");
                cardTableOneDate(conn, "2020-12-02", "2020-12-03");

                show("before receive 2020-12-03 card table.");
                cardTableOneDate(conn, "2020-12-03", "2020-12-04");

                show("before receive 2020-12-04 card table.");
                cardTableOneDate(conn, "2020-12-04", "2020-12-05");
            }

            // Truncate the existing table
            show("before receive route table.");
            routeTable(conn);

            show("before receive station table.");
            stationTable(conn);

            show("before receive card table.");
            cardTable(conn);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private void routeTable(Connection conn) {
        final String sql = "select ROUTE_ID, ROUTE_CODE, ORGAN_ID, ROUTE_NAME, ROUTE_NAMESHORT, ROUTE_TYPE, DISPATCH_MODE, " +
                "START_DATE, END_DATE, UP_STATION_COUNT, DOWN_STATION_COUNT, MAX_BUS_COUNT, MIN_BUS_COUNT, CURRENT_BUS_COUNT, PLAN_BUS_COUNT, " +
                "MONITOR_FLAG, RECORD_STATUS, DRAFTSMAN, DRAFT_TIME, AUDITMAN, AUDIT_TIME, AUDIT_RESULT, MILEAGE, IS_KEY_ROUTE, CHUCHANG_MODE, " +
                "BILL_MODE, PERCENT, C_ROUTE_CODE, IC_ROUTE_CODE, AUDIT_STATUS " +
                "FROM \"BASE.GJ_GJZN_ROUTE_MASTER_TABLE\"";
        try (Statement st = conn.createStatement()) {
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    GjznRoute gjznRoute = new GjznRoute();

                    Field[] fields = GjznRoute.class.getDeclaredFields();
                    int i = 1;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        if (field.getType().isAssignableFrom(String.class)) {
                            field.set(gjznRoute, rs.getString(i));
                        }
                        if (field.getType().isAssignableFrom(Integer.class)) {
                            field.set(gjznRoute, rs.getInt(i));
                        }
                        if (field.getType().isAssignableFrom(Date.class)) {
                            field.set(gjznRoute, rs.getDate(i));
                        }
                        if (field.getType().isAssignableFrom(BigDecimal.class)) {
                            field.set(gjznRoute, rs.getBigDecimal(i));
                        }
                        i++;
                    }

                    GjznRoute queryRoute = mysqlOperation.getGjznRouteMapper().getRoute(gjznRoute.getRouteId(), gjznRoute.getRouteCode(), gjznRoute.getRouteName());

                    if (BeanUtil.routeDataSameCheck(queryRoute, gjznRoute)) {
                        continue;
                    }
                    mysqlOperation.insertGjznRoute(gjznRoute);
                }
                show("prcess mysql route Table successfully.");
            }
        } catch (SQLException | IllegalAccessException e) {
            showError("select route table operation in oracle or insert into mysql route table failed .", e);
        }
    }

    private void stationTable(Connection conn) {
        final String sql = "select STATION_ID, ROUTE_ID, RUNNING_TYPE, STATION_TYPE, ORDER_NUMBER, STATION_NAME, NAME_SHORT, " +
                "ADDRESS, REGION_ID, STREET_ID, LONGITUDE, LATITUDE, ARRIVAL_RADIUS, DEPARTURE_RADIUS, MILEAGE, RUNNING_TIME, " +
                "SUM_MILEAGE, SUM_RUNNING_TIME, DRAFTSMAN, DRAFT_TIME, AUDITMAN, AUDIT_TIME, AUDIT_RESULT, STATION_CODE, " +
                "STATION_STATUS, RECORD_STATUS, BUS_OUT_TYPE, PAUSE_TIME, ORGAN_ID, STATION_GROUP_ID " +
                "FROM \"BASE.GJ_GJZN_STATION_MASTER_TABLE\"";
        try (Statement st = conn.createStatement()) {
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    GjznStation gjznStation = new GjznStation();

                    Field[] fields = GjznStation.class.getDeclaredFields();
                    int i = 1;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        if (field.getType().isAssignableFrom(String.class)) {
                            field.set(gjznStation, rs.getString(i));
                        }
                        if (field.getType().isAssignableFrom(Integer.class)) {
                            field.set(gjznStation, rs.getInt(i));
                        }
                        if (field.getType().isAssignableFrom(Date.class)) {
                            field.set(gjznStation, rs.getDate(i));
                        }
                        if (field.getType().isAssignableFrom(BigDecimal.class)) {
                            field.set(gjznStation, rs.getBigDecimal(i));
                        }
                        i++;
                    }

                    GjznStation queryStation = mysqlOperation.getGjznStationMapper().getStation(gjznStation.getStationId(),
                            gjznStation.getRouteId(), gjznStation.getStationName());

                    if (BeanUtil.stationDataSameCheck(queryStation, gjznStation)) {
                        continue;
                    }

                    mysqlOperation.insertGjznStation(gjznStation);
                }
                show("prcess mysql station Table successfully.");
            }
        } catch (SQLException | IllegalAccessException e) {
            showError("select station table operation in oracle or insert into mysql station table failed .", e);
        }
    }

    private void cardTable(Connection conn) {
        final String to_date = "to_date('" + queryDate + "','YYYY-MM-DD')";
        final String end_date = "to_date('" + queryEndDate + "','YYYY-MM-DD')";
        final String sql = "select BUSID, LINEID, USERTYPE, PARSEDATETIME, TRADEDATETIME, PROCESSDATETIME, " +
                "BUSNAME, LINENAME, STATIONNAME, STATIONID, RKSJ, TXNTYPE, CARDID, CARDCSN, POSOPRID, ORIGAMT, " +
                "TXNDATE, TXNTIME, UPTIME, UPBUSID, UPLINEID, UPSTATIONID, ISQR " +
                "FROM TONGTU.GJZN_CARDINFO WHERE TRADEDATETIME >= " + to_date + "AND TRADEDATETIME < " + end_date;
        try (Statement st = conn.createStatement()) {
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    GjznCard gjznCard = new GjznCard();

                    String result = null;

                    Field[] fields = GjznCard.class.getDeclaredFields();
                    int i = 1;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        if (field.getType().isAssignableFrom(String.class)) {
                            String sItem = rs.getString(i);
                            //show(field.getName() + " = " + sItem );
                            result += sItem + ",";
                            field.set(gjznCard, sItem);
                        }
                        if (field.getType().isAssignableFrom(Integer.class)) {
                            int iItem = rs.getInt(i);
                            //show(field.getName() + " = " + iItem );
                            result += iItem + ",";
                            field.set(gjznCard, iItem);
                        }
                        if (field.getType().isAssignableFrom(Date.class)) {
                            Date dItem = rs.getDate(i);
                            if (dItem != null) {
                                //show(field.getName() + " = " + dItem.toString() );
                                result += dItem.toString() + ",";
                            } else {
                                result += " ,";
                            }
                            field.set(gjznCard, dItem);
                        }
                        if (field.getType().isAssignableFrom(BigDecimal.class)) {
                            BigDecimal bgItem = rs.getBigDecimal(i);
                            //show(field.getName() + " = " + bgItem.toString() );
                            result += bgItem + ",";
                            field.set(gjznCard, bgItem);
                        }
                        i++;
                    }
                    result += "\n";

                    //this.writeCard2(JSON.toJSON(gjznCard).toString());
                    this.writeCard(result);

                }
                show("write card table into local card file " + cardFileName + " successfully.");
            }
        } catch (SQLException | IllegalAccessException | IOException e) {
            showError("select card table operation failed or write local card table file failed .", e);
        }
    }

    private void cardTableOneDate(Connection conn, String qDate, String qEndDate) {
        final String to_date = "to_date('" + qDate + "','YYYY-MM-DD')";
        final String end_date = "to_date('" + qEndDate + "','YYYY-MM-DD')";
        final String cardFileNameOneDate = System.getProperty("user.dir") + "/data/card/Card" + qDate.replace("-", "_") + ".txt";

        final String sql = "select BUSID, LINEID, USERTYPE, PARSEDATETIME, TRADEDATETIME, PROCESSDATETIME, " +
                "BUSNAME, LINENAME, STATIONNAME, STATIONID, RKSJ, TXNTYPE, CARDID, CARDCSN, POSOPRID, ORIGAMT, " +
                "TXNDATE, TXNTIME, UPTIME, UPBUSID, UPLINEID, UPSTATIONID, ISQR " +
                "FROM TONGTU.GJZN_CARDINFO WHERE TRADEDATETIME >= " + to_date + "AND TRADEDATETIME < " + end_date;
        try (Statement st = conn.createStatement()) {
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    GjznCard gjznCard = new GjznCard();

                    String result = null;

                    Field[] fields = GjznCard.class.getDeclaredFields();
                    int i = 1;
                    for (Field field : fields) {
                        field.setAccessible(true);
                        if (field.getType().isAssignableFrom(String.class)) {
                            String sItem = rs.getString(i);
                            //show(field.getName() + " = " + sItem );
                            result += sItem + ",";
                            field.set(gjznCard, sItem);
                        }
                        if (field.getType().isAssignableFrom(Integer.class)) {
                            int iItem = rs.getInt(i);
                            //show(field.getName() + " = " + iItem );
                            result += iItem + ",";
                            field.set(gjznCard, iItem);
                        }
                        if (field.getType().isAssignableFrom(Date.class)) {
                            Date dItem = rs.getDate(i);
                            if (dItem != null) {
                                //show(field.getName() + " = " + dItem.toString() );
                                result += dItem.toString() + ",";
                            } else {
                                result += " ,";
                            }
                            field.set(gjznCard, dItem);
                        }
                        if (field.getType().isAssignableFrom(BigDecimal.class)) {
                            BigDecimal bgItem = rs.getBigDecimal(i);
                            //show(field.getName() + " = " + bgItem.toString() );
                            result += bgItem + ",";
                            field.set(gjznCard, bgItem);
                        }
                        i++;
                    }
                    result += "\n";

                    //this.writeCard2(JSON.toJSON(gjznCard).toString());
                    this.writeCardWithFileName(cardFileNameOneDate, result);

                }
                show("write card table into local card file " + cardFileNameOneDate + " successfully.");
            }
        } catch (SQLException | IllegalAccessException | IOException e) {
            showError("select card table operation failed or write local card table file failed .", e);
        }
    }

    private void writeCard2(String response) {

        File file = new File(cardFileName);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file, true);
            byte[] bytes = (response + "\n").getBytes();
            for (byte aByte : bytes) {
                fos.write(aByte);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                assert fos != null;
                fos.flush(); //强制刷新输出流
                fos.close(); //强制关闭输出流
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void writeCard(String words) throws IOException {

        BufferedWriter bw = new BufferedWriter(new FileWriter(cardFileName));
        bw.write(words);
        bw.flush();
        bw.close();
        show("write one record " + words + " into " + cardFileName + " successfully.");
    }

    private void writeCardWithFileName(String fileName, String words) throws IOException {

        DataOutputStream dataOut = new DataOutputStream(new FileOutputStream(fileName));
        dataOut.writeUTF(words);
        dataOut.flush();
        dataOut.close();
    }

    private static void show(String msg) {
        System.out.println(msg);
    }

    static void showError(String msg, Throwable exc) {
        System.out.println(msg + " hit error: " + exc.getMessage());
    }
}
